package com.jeequan.jeepay.mgr.util;

import org.apache.commons.jexl3.JexlBuilder;
import org.apache.commons.jexl3.JexlEngine;
import org.apache.commons.lang3.StringUtils;
import org.jxls.command.CellDataUpdater;
import org.jxls.common.CellData;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.formula.StandardFormulaProcessor;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;



public class JxlsUtils {

    public static class TotalCellUpdater implements CellDataUpdater {

        public void updateCellData(CellData cellData, CellRef targetCell, Context context) {
            if( cellData.isFormulaCell() && cellData.getFormula().equals("SUM(C4)")){
                String resultFormula = String.format("SUM(C4:E%d)", targetCell.getRow());
                cellData.setEvaluationResult(resultFormula);
            }
        }

    }
    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {

        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }

        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer = jxlsHelper.createTransformer(is, os);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();

        //设置静默模式，不报警告
        //evaluator.getJexlEngine().setSilent(true);
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils());

        //添加自定义功能
        JexlEngine customJexlEngine = new JexlBuilder().namespaces(funcs).create();
        evaluator.setJexlEngine(customJexlEngine);

        //必须要这个，否者表格函数统计会错乱
        jxlsHelper.setProcessFormulas(true);
        jxlsHelper.setFormulaProcessor(new StandardFormulaProcessor()).setEvaluateFormulas(true);
        context.putVar("totalCellUpdater", new TotalCellUpdater());
        jxlsHelper.setUseFastFormulaProcessor(false).setEvaluateFormulas(true).processTemplate(context,transformer);
        jxlsHelper.setDeleteTemplateSheet(true);

    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = getTemplate(templatePath);
        if (template != null) {
            exportExcel(new FileInputStream(template), os, model);
        } else {
            throw new Exception("Excel 模板未找到。");
        }
    }

    //获取jxls模版文件
    public static File getTemplate(String path) {
        File template = new File(path);
        if (template.exists()) {
            return template;
        }
        return null;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

    //字符串分隔
    public String [] splitString(String fieldString){
        String [] groups=new String[2];
        String [] fields= fieldString.split("\\$",2);
        if(fields.length==2){
            groups[0]=fields[0];
            groups[1]=fields[1];
        }
        if(fields.length==1){
            groups[0]=fields[0];
            groups[1]="";
        }
        return groups;
    }


    /**
     * 以下为人民币大小写转换
     */

    // 大写数字
    private static final String[] NUMBERS = {"零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"};
    // 大写数字
    private static final String[] chineseNum = {"零", "一", "二", "三", "四", "五", "六", "七", "八", "九", "十"};
    // 整数部分的单位
    private static final String[] IUNIT = {"元", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "拾", "佰", "仟", "万", "拾", "佰", "仟"};
    // 小数部分的单位
    private static final String[] DUNIT = {"角", "分", "厘"};

    /**
     * 转换为大写的中文金额
     *
     * @param str 字符串类型的 金额数字
     * @return
     */
    public static String toChinese(String str) {
        // 判断输入的金额字符串是否符合要求
        if (StringUtils.isBlank(str) || !str.matches("(-)?[\\d]*(.)?[\\d]*")) {
            return "抱歉，请输入数字！";
        }

        if ("0".equals(str) || "0.00".equals(str) || "0.0".equals(str)) {
            return "零元";
        }

        // 判断金额数字中是否存在负号"-"
        boolean flag = false;
        if (str.startsWith("-")) {
            // 标志位，标志此金额数字为负数
            flag = true;
            str = str.replaceAll("-", "");
        }

        // 去掉金额数字中的逗号","
        str = str.replaceAll(",", "");
        String integerStr;//整数部分数字
        String decimalStr;//小数部分数字

        // 初始化：分离整数部分和小数部分
        if (str.indexOf(".") > 0) {
            integerStr = str.substring(0, str.indexOf("."));
            decimalStr = str.substring(str.indexOf(".") + 1);
        } else if (str.indexOf(".") == 0) {
            integerStr = "";
            decimalStr = str.substring(1);
        } else {
            integerStr = str;
            decimalStr = "";
        }

        // beyond超出计算能力，直接返回
        if (integerStr.length() > IUNIT.length) {
            return "超出计算能力！";
        }

        // 整数部分数字
        int[] integers = toIntArray(integerStr);
        // 判断整数部分是否存在输入012的情况
        if (integers.length > 1 && integers[0] == 0) {
            return "抱歉，输入数字不符合要求！";
        }
        // 设置万单位
        boolean isWan = isWan5(integerStr);
        // 小数部分数字
        int[] decimals = toIntArray(decimalStr);
        // 返回最终的大写金额
        String result = getChineseInteger(integers, isWan) + getChineseDecimal(decimals);
        if (StringUtils.isEmpty(getChineseDecimal(decimals))) {
            result = result + "整";
        }
        if (flag) {
            // 如果是负数，加上"负"
            return "负" + result;
        } else {
            return result;
        }
    }

    /**
     * 将字符串转为int数组
     *
     * @param number 数字
     * @return
     */
    private static int[] toIntArray(String number) {
        int[] array = new int[number.length()];
        for (int i = 0; i < number.length(); i++) {
            array[i] = Integer.parseInt(number.substring(i, i + 1));
        }
        return array;
    }

    /**
     * 将整数部分转为大写的金额
     *
     * @param integers 整数部分数字
     * @param isWan    整数部分是否已经是达到【万】
     * @return
     */
    public static String getChineseInteger(int[] integers, boolean isWan) {
        StringBuffer chineseInteger = new StringBuffer("");
        int length = integers.length;
        if (length == 1 && integers[0] == 0) {
            return "";
        }
        for (int i = 0; i < length; i++) {
            String key = "";
            if (integers[i] == 0) {
                if ((length - i) == 13)//万（亿）
                    key = IUNIT[4];
                else if ((length - i) == 9) {//亿
                    key = IUNIT[8];
                } else if ((length - i) == 5 && isWan) {//万
                    key = IUNIT[4];
                } else if ((length - i) == 1) {//元
                    key = IUNIT[0];
                }
                if ((length - i) > 1 && integers[i + 1] != 0) {
                    key += NUMBERS[0];
                }
            }
            chineseInteger.append(integers[i] == 0 ? key : (NUMBERS[integers[i]] + IUNIT[length - i - 1]));
        }
        return chineseInteger.toString();
    }

    /**
     * 将小数部分转为大写的金额
     *
     * @param decimals 小数部分的数字
     * @return
     */
    private static String getChineseDecimal(int[] decimals) {
        StringBuffer chineseDecimal = new StringBuffer("");
        for (int i = 0; i < decimals.length; i++) {
            if (i == 3) {
                break;
            }
            chineseDecimal.append(decimals[i] == 0 ? "" : (NUMBERS[decimals[i]] + DUNIT[i]));
        }
        return chineseDecimal.toString();
    }

    /**
     * 判断当前整数部分是否已经是达到【万】
     *
     * @param integerStr 整数部分数字
     * @return
     */
    private static boolean isWan5(String integerStr) {
        int length = integerStr.length();
        if (length > 4) {
            String subInteger = "";
            if (length > 8) {
                subInteger = integerStr.substring(length - 8, length - 4);
            } else {
                subInteger = integerStr.substring(0, length - 4);
            }
            return Integer.parseInt(subInteger) > 0;
        } else {
            return false;
        }
    }

    /**
     * 将数字转为中文大写
     *
     * @param number 数字
     * @return
     */
    public static String toChineseNumber(int number) {
        if (number >= 0 && number <= 10) {
            return chineseNum[number];
        }
        return "";
    }


    public static String convert(int number) {
        //将输入数字转换为字符串
        String result = String.valueOf(number);
        //将该字符串分割为数组存放
        char[] ch = result.toCharArray();
        //结果 字符串
        String str = "";
        int length = ch.length;
        for (int i = 0; i < length; i++) {
            int c = (int) ch[i] - 48;
            if (c != 0) {
                str += chineseNum[c - 1];
            }
        }
        return str;
    }

    ////交易类型,PERSONAL-个人支付,DEPARTMENTAL-部门支付，RENT-个人月租
    public static String getPayType(String str) {

        String convertStr=str.toUpperCase();
        if(convertStr.equals("RENT")){
            return "月租";
        } else if (str.equals("PERSONAL")) {
            return "个人支付";
        }else if(str.equals("DEPARTMENTAL")){
            return "部门支付";
        }else{
            return "未知";
        }
    }
}